﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.Runtime.Serialization;
using System.Security.Permissions;
using combit.ListLabel18.DataProviders;
using Oracle.DataAccess.Client;

namespace combit.ListLabel18.DataProviders.Oracle
{
    /// <summary>
    /// Provider for Oracle, see http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html
    /// Tested with version  11.1.0.7.20
    /// </summary>
    [Serializable]
    public sealed class OracleConnectionDataProvider : DbConnectionDataProvider, ISerializable
    {
        private ReadOnlyCollection<String> _tableOwners;

        private bool _prefixTableNameWithOwner;

        public bool PrefixTableNameWithOwner
        {
            get
            {
                return _prefixTableNameWithOwner;
            }
            set
            {
                _prefixTableNameWithOwner = value;
            }
        }


        public OracleConnectionDataProvider(OracleConnection connection)
            : this(connection, String.Empty)
        {
        }

        public OracleConnectionDataProvider(OracleConnection connection, string tableOwner)
        {
            Connection = connection;
            List<string> owners = new List<string>();
            if (!String.IsNullOrEmpty(tableOwner))
                owners.Add(tableOwner);
            _tableOwners = owners.AsReadOnly();
        }

        public OracleConnectionDataProvider(OracleConnection connection, ReadOnlyCollection<string> tableOwners)
        {
            Connection = connection;
            _tableOwners = tableOwners;
        }

        private OracleConnectionDataProvider()
            : base()
        {
        }

        private OracleConnectionDataProvider(SerializationInfo info, StreamingContext context)
            : base(info, context)
        {
            if (info.GetInt32("OracleConnectionDataProvider.Version") >= 1)
            {
                Connection = new OracleConnection();
                Connection.ConnectionString = info.GetString("ConnectionString");
                PrefixTableNameWithOwner = info.GetBoolean("PrefixTableNameWithOwner");
                _tableOwners = (ReadOnlyCollection<string>)info.GetValue("TableOwners", typeof(ReadOnlyCollection<string>));
            }
        }

        protected override void Init()
        {
            if (Initialized)
                return;

            List<String> passedRelationNames = new List<string>();
            List<String> excludedOwners = new List<string>();

            // see http://download.oracle.com/docs/cd/B28359_01/server.111/b28337/tdpsg_user_accounts.htm#BABGEGFI

            excludedOwners.Add("CTXSYS");
            excludedOwners.Add("DBSNMP");
            excludedOwners.Add("EXFSYS");
            excludedOwners.Add("FLOWS_020100");
            excludedOwners.Add("FLOWS_030000");
            excludedOwners.Add("FLOWS_FILES");
            excludedOwners.Add("IX");
            excludedOwners.Add("LBACSYS");
            excludedOwners.Add("MDSYS");
            excludedOwners.Add("MGMT_VIEW");
            excludedOwners.Add("OLAPSYS");
            excludedOwners.Add("OWBSYS");
            excludedOwners.Add("ORDPLUGINS");
            excludedOwners.Add("ORDSYS");
            excludedOwners.Add("OUTLN");
            excludedOwners.Add("SI_INFORMTN_SCHEMA");
            excludedOwners.Add("SYS");
            excludedOwners.Add("SYSMAN");
            excludedOwners.Add("SYSTEM");
            excludedOwners.Add("TSMSYS");
            excludedOwners.Add("WK_TEST");
            excludedOwners.Add("WKSYS");
            excludedOwners.Add("WKPROXY");
            excludedOwners.Add("WMSYS");
            excludedOwners.Add("XDB");

            foreach (string owner in _tableOwners)
            {
                if (excludedOwners.Contains(owner))
                    excludedOwners.Remove(owner);
            }

            string primaryKeyQuery = "SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner FROM all_constraints cons, all_cons_columns cols WHERE cols.table_name = '{0}' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = '{1}' AND cols.owner = '{1}' ORDER BY cols.table_name, cols.position";

            Connection.Open();
            try
            {
                DataTable tables = (Connection as DbConnection).GetSchema("Tables");
                DataTable foreignKeys = (Connection as DbConnection).GetSchema("ForeignKeys");
                DataTable foreignKeyColumns = (Connection as DbConnection).GetSchema("ForeignKeyColumns");

                foreach (DataRow dr in tables.Rows)
                {
                    string tableSchema = dr["OWNER"].ToString();
                    string parentTableName = dr["TABLE_NAME"].ToString();

                    if ((_tableOwners.Count != 0) && !(_tableOwners.Contains(tableSchema)))
                        continue;

                    if (excludedOwners.Contains(tableSchema))
                        continue;

                    if (parentTableName.Contains("$"))
                        continue;

                    DbConnection newConnection = (((Connection) as ICloneable).Clone() as DbConnection);
                    DbCommand newCmd = newConnection.CreateCommand();
                    newCmd.CommandText = "Select * From \"" + (String.IsNullOrEmpty(tableSchema) ? parentTableName + "\"" : tableSchema + "\".\"" + parentTableName) + "\"";

                    Provider.AddCommand(newCmd as IDbCommand, PrefixTableNameWithOwner ? String.Concat(tableSchema, ".", parentTableName) : parentTableName);

                    // pass relations
                    string commandText = String.Format(CultureInfo.InvariantCulture, primaryKeyQuery, parentTableName, tableSchema);

                    DbCommand cmd = (Connection as DbConnection).CreateCommand();
                    cmd.CommandText = commandText;
                    DbDataReader reader = cmd.ExecuteReader();
                    string parentColumnName = String.Empty;
                    while (reader.Read())
                    {
                        if (!String.IsNullOrEmpty(parentColumnName))
                            parentColumnName += '\t';
                        parentColumnName += reader.GetString(1);
                    }
                    reader.Close();

                    // the foreignKeys Schema table contains the assigned foreign keys. We need to find
                    // all child tables to the current table, and additionally the column names. These are
                    // hidden in the ForeignKeyColumns Schema table...
                    foreach (DataRow keyRow in foreignKeys.Rows)
                    {
                        if ((keyRow["PRIMARY_KEY_TABLE_NAME"].ToString() == parentTableName) &&
                            (keyRow["PRIMARY_KEY_OWNER"].ToString() == tableSchema))
                        {
                            // we have a hit
                            string childTableName = keyRow["FOREIGN_KEY_TABLE_NAME"].ToString();
                            string constraintName = keyRow["FOREIGN_KEY_CONSTRAINT_NAME"].ToString();
                            string childTableOwner = keyRow["FOREIGN_KEY_OWNER"].ToString();

                            if ((_tableOwners.Count != 0) && !(_tableOwners.Contains(childTableOwner)))
                                continue;

                            if (excludedOwners.Contains(childTableOwner))
                                continue;


                            // now look up the child column name
                            string childColumnName = String.Empty;
                            foreach (DataRow keyColumnRow in foreignKeyColumns.Rows)
                            {
                                if (!(keyColumnRow["CONSTRAINT_NAME"].ToString() == constraintName))
                                    continue;
                                if (!(keyColumnRow["OWNER"].ToString() == tableSchema))
                                    continue;
                                if (!String.IsNullOrEmpty(childColumnName))
                                    childColumnName += '\t';
                                childColumnName += keyColumnRow["COLUMN_NAME"];
                            }

                            string relName = parentTableName + "2" + childTableName;
                            int relationIndex = 1;
                            string formatString = relName + "{0}";

                            while (passedRelationNames.Contains(relName))
                            {
                                relName = String.Format(CultureInfo.InvariantCulture, formatString, relationIndex);
                                relationIndex++;
                            }
                            passedRelationNames.Add(relName);
                            Provider.AddRelation(relName,
                                                 PrefixTableNameWithOwner ? String.Concat(tableSchema, ".", parentTableName) : parentTableName,
                                                 PrefixTableNameWithOwner ? String.Concat(childTableOwner, ".", childTableName) : childTableName,
                                                 parentColumnName,
                                                 childColumnName);
                        }
                    }
                }
            }
            finally
            {
                Connection.Close();
                Initialized = true;
            }
        }

        #region ISerializable Members

        [SecurityPermission(SecurityAction.Demand, SerializationFormatter = true)]
        public override void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            base.GetObjectData(info, context);
            info.AddValue("OracleConnectionDataProvider.Version", 1);
            info.AddValue("ConnectionString", Connection.ConnectionString);
            info.AddValue("PrefixTableNameWithOwner", PrefixTableNameWithOwner);
            info.AddValue("TableOwners", _tableOwners);
        }

        #endregion
    }
}
